from django.test import TestCase
from booktest.models import BookInfo,HeroInfo

from django.db.models import *
from datetime import date
# Create your tests here.

class ModelTest(TestCase):

    def setUp(self):  # 初始化，创建测试数据
        #图书表
        book1 = BookInfo()
        book1.btitle = '天龙八部2'
        book1.bpub_date = date(2022,12,21)
        book1.isDelete = False
        book1.bread = 12

        book2 = BookInfo()
        book2.btitle = '笑傲江湖'
        book2.bpub_date = date(2021, 11, 22)
        book1.isDelete = False
        book2.bread = 6

        book3 = BookInfo()
        book3.btitle = '测试部门2'
        book3.bpub_date = date(2021, 8, 30)
        book1.isDelete = True

        book1.save()
        book2.save()
        book3.save()

        #英雄表
        hero1 = HeroInfo()
        hero1.hname = "乔峰"
        hero1.hcomment = "降龙十八掌"
        hero1.hbook = book1

        hero2 = HeroInfo()
        hero2.hname = "段誉"
        hero2.hcomment = "六脉神剑"
        hero2.hbook = book1

        hero1.save()
        hero2.save()


    def test1(self):
        print("进入查询所有的方法1")
        list = BookInfo.objects.all()
        for book in list:
            print(str(book.id)+":"+book.btitle)

    def test2(self):
        print("进入--查询等 exact：表示判等。例：查询编号为1的图书。")
        list = BookInfo.objects.filter(id__exact=1)
        for book in list:
            print(str(book.id)+":"+book.btitle)

    def test3(self):
        print("进入--模糊查询 contains：是否包含 为 '部'")
        list = BookInfo.objects.filter(btitle__contains='部')
        for book in list:
            print(str(book.id)+":"+book.btitle)

        print("进入--查询书名以'部'结尾的图书")
        list2 = BookInfo.objects.filter(btitle__endswith='部')
        for book in list2:
            print(str(book.id)+":"+book.btitle)

        print("进入--查询书名以'天'开头的图书")
        list3 = BookInfo.objects.filter(btitle__startswith='天')
        for book in list3:
            print(str(book.id) + ":" + book.btitle)
    def test4(self):
        print("进入--空查询 isnull：是否为null。")
        list = BookInfo.objects.filter(bread__isnull=True)
        for book in list:
            print(str(book.id)+":"+book.btitle)
    def test5(self):
        print("进入--范围查询 in：是否包含在范围内。例：查询编号为1或3或5的图书")
        list = BookInfo.objects.filter(id__in=[1, 3])
        for book in list:
            print(str(book.id) + ":" + book.btitle)

    def test6(self):
        print("进入--比较查询 gt、gte、lt、lte：大于、大于等于、小于、小于等于。例：查询编号大于2的图书")
        list = BookInfo.objects.filter(id__gt=2)
        for book in list:
            print(str(book.id) + ":" + book.btitle)

    def test7(self):
        print("进入--日期查询 year、month、day、week_day、hour、minute、second：对日期时间类型的属性进行运算。例：查询1980年发表的图书。")
        #list = BookInfo.objects.filter(bpub_date__year=2021)
        list = BookInfo.objects.filter(bpub_date__gt=date(2021, 10, 1))
        for book in list:
            print(str(book.id) + ":" + book.btitle)

    def test8(self):
        print("进入--F对象。例：查询阅读量大于等于评论量的图书。")
        list = BookInfo.objects.filter(bread__gte=F('bcomment'))
        for book in list:
            print(str(book.id) + ":" + book.btitle)

        print("进入--多条件查询")
        list = BookInfo.objects.filter(bread__gt=10, id__lt=3)
        #list = BookInfo.objects.filter(bread__gt=20).filter(id__lt=3)
        for book in list:
            print(str(book.id) + ":" + book.btitle)

        print("进入--Q对象可以使用&、|连接，&表示逻辑与，|表示逻辑或。")
        #list = BookInfo.objects.filter(Q(bread__gt=10))
        list = BookInfo.objects.filter(Q(bread__gt=10) & Q(pk__lt=3))
        for book in list:
            print(str(book.id) + ":" + book.btitle)

    def test9(self):
        print("进入--聚合函数 使用aggregate()过滤器调用聚合函数。聚合函数包括：Avg，Count，Max，Min，Sum，被定义在django.db.models中")
        list = BookInfo.objects.aggregate(Sum('bread'))
        print(list)



    def test10(self):
        print("限制查询集")
        list = BookInfo.objects.all()[0:1]
        print(list)

        print("通过模型类执行关联查询 ---由一到多的访问语法： ")
        b = BookInfo.objects.get(id=1)
        list = b.heroinfo_set.all()
        for hero in list:
            print(str(hero.id) + ":" + hero.hname+ ":" + hero.hcomment)

        print("通过模型类执行关联查询 ---由多到一的访问语法:： ")
        h = HeroInfo.objects.get(id=1)
        print("图书名称："+h.hbook.btitle)

        print("通过模型类执行关联查询 ---由多模型类条件查询一模型类数据:(关联模型类名小写__属性名__条件运算符=值) ")
        list = BookInfo.objects.filter(heroinfo__hcomment__contains='八')
        for book in list:
            print(str(book.id) + ":" + book.btitle)

        list = HeroInfo.objects.filter(hbook__btitle='天龙八部')
        for hero in list:
            print(str(hero.id) + ":" + hero.hname+ ":" + hero.hcomment)
    def test11(self):
        print("修改原始查询集，重写all()方法 ")
        list = BookInfo.books.all()
        for book in list:
            print(str(book.id) + ":" + book.btitle)

        print("向管理器类中添加额外的方法，如向数据库中插入数据 ")
        book = BookInfo.books.create_book("abc", date(2022, 1, 1))
        list = BookInfo.books.all()
        for book in list:
            print(str(book.id) + ":" + book.btitle)